﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.CommonFilter;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using LBKDHelper.Models;
using LBKDHelper.PluginHelpers;
using LBKDHelper.PluginTemplates;
using LM.CB.K3Cloud.Plugin.Models.Grasses;
using LMCommonage.Configurations;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;

namespace LM.CB.K3Cloud.Plugin
{
    /// <summary>
    /// 目标成本报表
    /// </summary>
    /// <remarks>
    /// 鹿奔 240517
    /// </remarks>
    [Description("目标成本报表插件"), HotUpdate]
    public class TargetCostReportPlugin : ReportPluginTemplate
    {
        private string _sourceTableName { get; set; }

        #region MyRegion

        /// <summary>
        /// 过滤-组织
        /// </summary>
        readonly ElementKeySchema F_QLTK_org = new ElementKeySchema
        {
            FormKey = "F_QLTK_org",
            OrmKey = "F_QLTK_org"
        };

        /// <summary>
        /// 过滤-年
        /// </summary>
        readonly ElementKeySchema F_QLTK_year = new ElementKeySchema
        {
            FormKey = "F_QLTK_year",
            OrmKey = "F_QLTK_year"
        };

        /// <summary>
        /// 过滤-月
        /// </summary>
        readonly ElementKeySchema F_QLTK_month = new ElementKeySchema
        {
            FormKey = "F_QLTK_month",
            OrmKey = "F_QLTK_month"
        };

        /// <summary>
        /// 过滤-截止年
        /// </summary>
        readonly ElementKeySchema F_QLTK_year_qtr = new ElementKeySchema
        {
            FormKey = "F_QLTK_year_qtr",
            OrmKey = "F_QLTK_year_qtr"
        };

        /// <summary>
        /// 过滤-截止月
        /// </summary>
        readonly ElementKeySchema F_QLTK_month_83g = new ElementKeySchema
        {
            FormKey = "F_QLTK_month_83g",
            OrmKey = "F_QLTK_month_83g"
        };

        /// <summary>
        /// 报表-截止年
        /// </summary>
        readonly ElementKeySchema F_QLTK_year_re5 = new ElementKeySchema
        {
            FormKey = "F_QLTK_year_re5",
            OrmKey = "F_QLTK_year_re5"
        };

        /// <summary>
        /// 报表-截止月
        /// </summary>
        readonly ElementKeySchema F_QLTK_month_apv = new ElementKeySchema
        {
            FormKey = "F_QLTK_month_apv",
            OrmKey = "F_QLTK_month_apv"
        };

        /// <summary>
        /// 过滤-产品
        /// </summary>
        readonly ElementKeySchema F_QLTK_Base_qtr = new ElementKeySchema
        {
            FormKey = "F_QLTK_Base_qtr",
            OrmKey = "F_QLTK_Base_qtr"
        };

        /// <summary>
        /// 报表-产品
        /// </summary>
        readonly ElementKeySchema F_QLTK_Text_re5 = new ElementKeySchema
        {
            FormKey = "F_QLTK_Text_re5",
            OrmKey = "F_QLTK_Text_re5"
        };

        #endregion

        public TargetCostReportPlugin()
            : base()
        { }

        public override void Initialize()
        {
            // 不在 UI 设计列
            ReportProperty.IsUIDesignerColumns = false;
            // 账表名称
            ReportProperty.ReportName = new LocaleValue("目标成本报表");
            // 设置精度
            var decimalControlFieldList = new List<DecimalControlField>
            {
                new DecimalControlField("decimal_length", "实际数量")
            };
            ReportProperty.DecimalControlFieldList = decimalControlFieldList;
        }

        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            header.AddChild("成本中心编码", new LocaleValue("成本中心编码", Context.UserLocale.LCID));
            header.AddChild("成本中心名称", new LocaleValue("成本中心名称", Context.UserLocale.LCID));

            header.AddChild("产品编码", new LocaleValue("产品编码", Context.UserLocale.LCID));
            header.AddChild("产品名称", new LocaleValue("产品名称", Context.UserLocale.LCID));
            header.AddChild("规格型号", new LocaleValue("规格型号", Context.UserLocale.LCID));
            header.AddChild("基本单位名称", new LocaleValue("基本单位", Context.UserLocale.LCID));
            header.AddChild("分组名称1", new LocaleValue("产品组", Context.UserLocale.LCID));
            header.AddChild("分组名称2", new LocaleValue("产品分配", Context.UserLocale.LCID));
            header.AddChild("分组名称3", new LocaleValue("物料组", Context.UserLocale.LCID));

            header.AddChild("成本项目编码", new LocaleValue("成本项目编码", Context.UserLocale.LCID));
            header.AddChild("成本项目名称", new LocaleValue("成本项目名称", Context.UserLocale.LCID));

            header.AddChild("原料编码", new LocaleValue("原料编码", Context.UserLocale.LCID));
            header.AddChild("原料名称", new LocaleValue("原料名称", Context.UserLocale.LCID));

            header.AddChild("实际产量", new LocaleValue("实际产量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("实际总成本", new LocaleValue("实际成本", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("实际单价", new LocaleValue("实际单价", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("实际总消耗", new LocaleValue("实际总消耗", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("实际单位成本", new LocaleValue("实际单位成本", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("实际单耗", new LocaleValue("实际单耗", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            //header.AddChild("实际数量", new LocaleValue("实际数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;

            //header.AddChild("目标数量", new LocaleValue("目标数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("目标总成本", new LocaleValue("目标成本", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("目标单位成本", new LocaleValue("目标单位成本", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("目标总消耗量", new LocaleValue("目标总消耗量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("目标单耗", new LocaleValue("目标单耗", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;

            header.AddChild("超降额", new LocaleValue("超降额", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;
            header.AddChild("超降率", new LocaleValue("超降率（%）", Context.UserLocale.LCID), SqlStorageType.SqlDecimal).Decimal = 6;

            //header.AddChild("工单编号", new LocaleValue("工单编号", Context.UserLocale.LCID));
            //header.AddChild("工单行号", new LocaleValue("工单行号", Context.UserLocale.LCID));
            //header.AddChild("物料号", new LocaleValue("物料号", Context.UserLocale.LCID));
            //header.AddChild("物料描述", new LocaleValue("物料描述", Context.UserLocale.LCID));
            //header.AddChild("生产版本", new LocaleValue("生产版本", Context.UserLocale.LCID));
            //header.AddChild("基本单位", new LocaleValue("基本单位", Context.UserLocale.LCID));
            //header.AddChild("产品组", new LocaleValue("产品组", Context.UserLocale.LCID));
            //header.AddChild("产品分配", new LocaleValue("产品分配", Context.UserLocale.LCID));
            //header.AddChild("物料组", new LocaleValue("物料组", Context.UserLocale.LCID));
            ////header.AddChild("生产调度员", new LocaleValue("生产调度员", Context.UserLocale.LCID));
            //header.AddChild("成本中心", new LocaleValue("成本中心", Context.UserLocale.LCID));
            //header.AddChild("成本中心描述", new LocaleValue("成本中心描述", Context.UserLocale.LCID));
            //header.AddChild("目标产量", new LocaleValue("目标产量", Context.UserLocale.LCID));
            //header.AddChild("总目标成本", new LocaleValue("总目标成本", Context.UserLocale.LCID));
            //header.AddChild("目标单位成本", new LocaleValue("目标单位成本", Context.UserLocale.LCID));
            //header.AddChild("标准成本", new LocaleValue("标准成本", Context.UserLocale.LCID));
            //header.AddChild("实际产量", new LocaleValue("实际产量", Context.UserLocale.LCID));
            //header.AddChild("实际单价", new LocaleValue("实际单位成本", Context.UserLocale.LCID));
            //header.AddChild("总不变价成本", new LocaleValue("总不变价成本", Context.UserLocale.LCID));
            //header.AddChild("不变价单位成本", new LocaleValue("不变价单位成本", Context.UserLocale.LCID));
            //header.AddChild("成本要素", new LocaleValue("成本要素", Context.UserLocale.LCID));
            //header.AddChild("组件", new LocaleValue("组件", Context.UserLocale.LCID));
            //header.AddChild("组件描述", new LocaleValue("组件描述", Context.UserLocale.LCID));
            //header.AddChild("组件标准成本", new LocaleValue("组件标准成本", Context.UserLocale.LCID));
            //header.AddChild("目标单耗", new LocaleValue("目标单耗", Context.UserLocale.LCID));
            //header.AddChild("目标单价", new LocaleValue("目标单价", Context.UserLocale.LCID));
            //header.AddChild("实际单耗", new LocaleValue("实际单耗", Context.UserLocale.LCID));
            //header.AddChild("实际组件单位成本", new LocaleValue("实际组件单位成本", Context.UserLocale.LCID));
            //header.AddChild("不变价成本", new LocaleValue("不变价成本", Context.UserLocale.LCID));
            //header.AddChild("不变价成本与目标成本差异", new LocaleValue("不变价成本与目标成本差异", Context.UserLocale.LCID));
            //header.AddChild("不变价成本超降率", new LocaleValue("不变价成本超降率(%)", Context.UserLocale.LCID));
            //header.AddChild("组件调度员", new LocaleValue("组件调度员", Context.UserLocale.LCID));
            //header.AddChild("组件标识", new LocaleValue("组件标识", Context.UserLocale.LCID));
            return header;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);

            // 指定默认的排序字段
            var seqField = GetOrderColumnSQL(filter, "b.FNUMBER, a.产品编码");
            // 获取自定义过滤框内的过滤条件
            var customerFilters = filter.FilterParameter.CustomFilter;
            var orgId = Convert.ToInt32((customerFilters[F_QLTK_org.OrmKey] as DynamicObject)?["Id"]);
            var year = Convert.ToInt16(customerFilters[F_QLTK_year.OrmKey]);
            var month = Convert.ToInt16(customerFilters[F_QLTK_month.OrmKey]);
            var endYear = Convert.ToInt16(customerFilters[F_QLTK_year_qtr.OrmKey]);
            var endMonth = Convert.ToInt16(customerFilters[F_QLTK_month_83g.OrmKey]);
            var productId = Convert.ToInt32((customerFilters[F_QLTK_Base_qtr.OrmKey] as DynamicObject)?["Id"]);

            // 创建临时表，用以存储标准报表数据
            _sourceTableName = $"CBJSD{DateTime.Now:yyMMddHHmmssfff}_" + PluginUtils.GetTempTableName(Context);
            FillCostData(new anva11
            {
                ProductId = productId,
                OrgId = orgId,
                EndMonth = endMonth,
                EndYear = endYear,
                StartMonth = month,
                StartYear = year
            }, _sourceTableName);

            /*
             读取成本计算单作为基础数据


            材料目标成本：
            产品实际产量=成本计算单的产品本期完工数量（按成本中心和产品进行汇总）
            原料实际消耗=成本计算单的原料本期完工数量（按成本中心、产品、成本项目、原料进行汇总）
            原料实际成本=成本计算单的原料本期完工金额（按成本中心、产品、成本项目、原料进行汇总）
            原料单位成本=其实际消耗/其对应产品的实际产量
            原料实际单耗=其实际成本/其对应产品的实际产量
            原料实际单价=其实际成本/其实际消耗（如果当期因没有消耗而无法计算实际单价，则取其累计投入金额/累计投入数量）

            原料目标消耗=产品当期所有生产订单（或委外生产订单）对应的生产用料清单的基本单位应发数量总和
            原料目标成本=其目标消耗*其实际单价
            原料目标单位成本=其目标成本/其对应产品的实际产量
            原料目标单耗=其目标消耗/其对应产品的实际产量

            超降额=各原料的实际总成本-其目标总成本
            超降率=各原料的超降额/其目标总成本


            费用目标成本：
            先计算 单个产品的各费用耗用量：工艺路线的耗用量/工艺路线的产品数量
            再计算 单个产品的各费用金额：单个产品的各费用耗用量*标准费率表的单价
            再计算 产品各费用总耗用量：单个产品的各费用金额*成本计算单的本期完工数量

             */
            // 构建查询SQL，最后一个字段是排序字段，并且数据要 into 到插件提供的临时表内
            var sql = $@"/*dialect*/
if object_id('tempdb..#成本计算单', 'U') is not null
	drop table #成本计算单;
if object_id('tempdb..#产品成本计算单', 'U') is not null
	drop table #产品成本计算单;
if object_id('tempdb..#子项成本计算单', 'U') is not null
	drop table #子项成本计算单;
if object_id('tempdb..#材料类成本计算单', 'U') is not null
	drop table #材料类成本计算单;
if object_id('tempdb..#各产品的各原料总投入', 'U') is not null
	drop table #各产品的各原料总投入;
if object_id('tempdb..#各产品总产量', 'U') is not null
	drop table #各产品总产量;
if object_id('tempdb..#各产品的各原料总投入2', 'U') is not null
	drop table #各产品的各原料总投入2;
if object_id('tempdb..#各工单的原料目标数量', 'U') is not null
	drop table #各工单的原料目标数量;
if object_id('tempdb..#各产品的各原料总投入3', 'U') is not null
	drop table #各产品的各原料总投入3;
if object_id('tempdb..#最新标准费率', 'U') is not null
	drop table #最新标准费率;
if object_id('tempdb..#各产品的各费用总成本', 'U') is not null
	drop table #各产品的各费用总成本;
if object_id('tempdb..#各产品的各费用总成本2', 'U') is not null
	drop table #各产品的各费用总成本2;
if object_id('tempdb..#物料分组', 'U') is not null
	drop table #物料分组;
if object_id('tempdb..#物料三级分组', 'U') is not null
	drop table #物料三级分组;
if object_id('tempdb..#物料', 'U') is not null
	drop table #物料;
if object_id('tempdb..#初始成本计算单', 'U') is not null
	drop table #初始成本计算单;
if object_id('tempdb..#有效的工单分录', 'U') is not null
	drop table #有效的工单分录;
if object_id('tempdb..#成本项目匹配方案', 'U') is not null
	drop table #成本项目匹配方案;
if object_id('tempdb..#成本项目匹配方案2', 'U') is not null
	drop table #成本项目匹配方案2;
if object_id('tempdb..#工单分录成本单耗量', 'U') is not null
	drop table #工单分录成本单耗量;
if object_id('tempdb..#费用类成本项目计算单', 'U') is not null
	drop table #费用类成本项目计算单;
if object_id('tempdb..#工单分录成本单耗量2', 'U') is not null
	drop table #工单分录成本单耗量2;
if object_id('tempdb..#费用类成本项目计算单2', 'U') is not null
	drop table #费用类成本项目计算单2;
if object_id('tempdb..#成本项目成本数据', 'U') is not null
	drop table #成本项目成本数据;
if object_id('tempdb..#材料类成本计算单2', 'U') is not null
	drop table #材料类成本计算单2;

declare @orgId int = {orgId};

select *
into #初始成本计算单
from {_sourceTableName};

select distinct 工单编号, 工单行号
into #有效的工单分录
from #初始成本计算单
where 单据类型 not in ('直接入库-二次包装生产', '直接入库-返工生产')
-- 实际产量要大于0才有意义
and 本期完工_数量 > 0;

/******************************* 有效的成本计算单数据（剔除 本期完工_数量 为0的数据） */
select a.*
into #成本计算单
from #初始成本计算单 as a
inner join #有效的工单分录 as b on b.工单编号 = a.工单编号 and b.工单行号 = a.工单行号;

/****************************** 成本计算单中各产品的小计行（成本项目没有值则为产品小计行） */
select *
into #产品成本计算单
from #成本计算单
-- 成本项目空着的数据，是产品总数居
where len(成本项目编码) !> 0
-- 实际产量要大于0才有意义
and 本期完工_数量 > 0;

/************************************************** 分组统计各产品的总产量 */
select 成本中心内码, 产品编码
	, '实际产量' = sum(本期完工_数量)
into #各产品总产量
from #产品成本计算单
group by 成本中心内码, 产品编码;

/******************************************* 获取所有子项的数据（成本项目字段有值则为原料数据） */
select a.*
	, c.FISMATERIAL as '是否为材料类成本项目'
into #子项成本计算单
from #成本计算单 as a
-- 成本项目表
inner join T_HS_COSTITEM as c on c.FNUMBER = a.成本项目编码;

/************************************************************** 材料成本 */
/*********************************** 获取原料的小计行（费用项目字段没有值，则为原料的小计行） */
select a.*
-- 	, b.FMATERIALID as '原料内码'
	-- 给每个产品的各个原料分配一个ID，按 工单编号、工单行号 分组，以字段 原料编码 排序
	, row_number() over (partition by a.工单编号, a.工单行号 order by a.原料编码) as idx
into #材料类成本计算单
from #子项成本计算单 as a
-- inner join T_BD_MATERIAL as b on b.FUSEORGID = a.组织内码 and b.FNUMBER = a.原料编码
where a.是否为材料类成本项目 = 1
	and a.原料编码 != ''
	and len(a.费用项目编码) !> 0
-- 	and a.费用项目编码 != ''
	and 1 = 1;

/***************************************** 获取各产品用料清单（在成本计算单中有数据的才取）内各原料的实际用量等信息 */
select b.组织内码, b.年期
	, b.成本中心内码, b.成本中心编码
	, b.工单编号, b.工单类型, b.工单行号
	, b.产品内码, b.产品编码
	, b.成本项目编码, b.费用项目编码
	, a.FMATERIALID as '原料内码', d.FNUMBER as '原料编码'
	, '本期完工_数量' = iif(e.idx is null, 0, e.本期完工_数量)
	, '本期完工_单耗' = iif(e.idx is null, 0, e.本期完工_单耗)
	, '本期完工_金额' = iif(e.idx is null, 0, e.本期完工_金额)
	, '本期完工_单位成本' = iif(e.idx is null, 0, e.本期完工_单位成本)
	, b.单据类型, b.是否为材料类成本项目
	, '累计投入_数量' = iif(e.idx is null, 0, e.累计投入_数量)
	, '累计投入_金额' = iif(e.idx is null, 0, e.累计投入_金额)
	--, row_number() over (partition by b.工单编号, b.工单行号 order by c.FMATERIALID) as idx -- 按 a、b 分组，以字段 c 排序
into #材料类成本计算单2
from (
	select distinct a.FMOBILLNO, a.FMOENTRYSEQ, c.FMATERIALID
	from T_PRD_PPBOM as a
	inner join T_PRD_PPBOMENTRY as c on c.FID = a.FID
) as a
inner join T_BD_MATERIAL as d on d.FMATERIALID = a.FMATERIALID
inner join #材料类成本计算单 as b on b.idx = 1 and b.工单编号 = a.FMOBILLNO and b.工单行号 = a.FMOENTRYSEQ
left join #材料类成本计算单 as e on e.工单编号 = a.FMOBILLNO and e.工单行号 = a.FMOENTRYSEQ and e.原料编码 = d.FNUMBER;

/*********************************************** 汇总各产品的同一原料的实际消耗 */
select a.成本中心内码, a.产品编码, a.成本项目编码, a.原料编码
	, '实际总消耗' = sum(a.本期完工_数量)
	, '实际总成本' = sum(a.本期完工_金额)
	, '累计总消耗' = sum(a.累计投入_数量)
	, '累计总成本' = sum(a.累计投入_金额)
into #各产品的各原料总投入
from #材料类成本计算单2 as a
group by a.成本中心内码, a.产品编码, a.成本项目编码, a.原料编码;

select *
	, '实际单耗' = iif(实际产量 != 0, 实际总消耗 / 实际产量, 0)
into #各产品的各原料总投入2
from (
	select a.*
		, b.实际产量
		, '实际单价' = iif(a.实际总消耗 != 0, a.实际总成本 / a.实际总消耗, iif(a.累计总消耗 != 0, a.累计总成本 / a.累计总消耗, 0))
		, '实际单位成本' = iif(b.实际产量 != 0, a.实际总成本 / b.实际产量, 0)
	from #各产品的各原料总投入 as a
	inner join #各产品总产量 as b on b.成本中心内码 = a.成本中心内码 and b.产品编码 = a.产品编码
) t;

select a.成本中心内码, a.成本项目编码, a.产品编码, a.原料编码
	, '目标数量' = sum(a.目标数量)
into #各工单的原料目标数量
from (
	select a.成本中心内码, a.成本项目编码, a.产品编码, a.原料编码
		, '目标数量' = sum(c.FBASEMUSTQTY/* 基本单位应发数量 */)
	from #材料类成本计算单2 as a
	-- 委外用料清单
	inner join T_SUB_PPBOM as b on b.FSUBBILLNO = a.工单编号 and b.FSUBREQENTRYSEQ = a.工单行号
	inner join T_SUB_PPBOMENTRY as c on c.FID = b.FID and c.FMATERIALID = a.原料内码
	where a.工单类型 = 'OUTSRC'
	group by a.成本中心内码, a.成本项目编码, a.产品编码, a.原料编码
	
	union all
	
	select a.成本中心内码, a.成本项目编码, a.产品编码, a.原料编码
		, '目标数量' = sum(c.FBASEMUSTQTY)
	from #材料类成本计算单2 as a
	-- 生产用料清单
	inner join T_PRD_PPBOM as b on b.FMOBILLNO = a.工单编号 and b.FMOENTRYSEQ = a.工单行号
	inner join T_PRD_PPBOMENTRY as c on c.FID = b.FID and c.FMATERIALID = a.原料内码
	where a.工单类型 = 'PO'
	group by a.成本中心内码, a.成本项目编码, a.产品编码, a.原料编码
) as a
group by a.成本中心内码, a.成本项目编码, a.产品编码, a.原料编码;

select *
	, '超降率' = iif(目标总成本 != 0, 超降额 / 目标总成本, 0)
into #各产品的各原料总投入3
from (
	select *
		, '目标单耗' = iif(实际产量 != 0, 目标总消耗量 / 实际产量, 0)
		, '超降额' = 实际总成本 - 目标总成本
	from (
		select *
			, '目标单位成本' = iif(实际产量 != 0, 目标总成本 / 实际产量, 0)
		from (
			select a.*
				, '目标总消耗量' = b.目标数量
				, '目标总成本' = b.目标数量 * a.实际单价
			from #各产品的各原料总投入2 as a
			inner join #各工单的原料目标数量 as b
				on b.成本中心内码 = a.成本中心内码
				and b.成本项目编码 = a.成本项目编码
				and b.产品编码 = a.产品编码 and b.原料编码 = a.原料编码
		) t
	) t
) t;

/************************************************************** 费用成本 */
select b.FENTRYID, b.FCOSTITEMID as '成本项目内码', b.FCOSTCENTERPRO as '成本中心属性'
into #成本项目匹配方案
from T_HS_COSTEXPITEMREL as a
inner join T_HS_COSTEXPITEMRELENTRY as b on b.FID = a.FID
where a.FAPPLYORGID = @orgId;

select *
into #成本项目匹配方案2
from (
	-- 基本生产=4：费用项目
	select a.FEXPENSEITEMID as '费用项目或作业活动内码'
		, b.*
	from T_HS_COSTEXPITEMRELENTRYEXP as a
	inner join #成本项目匹配方案 as b on b.FENTRYID = a.FENTRYID
	
	union all
	
	select a.FWORKACTIVITEID
		, b.*
	from T_HS_COSTEXPITEMRELENTRYWRK as a
	inner join #成本项目匹配方案 as b on b.FENTRYID = a.FENTRYID
) t;

select a.F_DEV_FYXMBM as '费用项目内码'--, b.FNUMBER as '费用项目编码'
	, a.F_DEV_PRICE as '单价'
into #最新标准费率
-- 标准费率表分录
from DEV_t_StandardRate_E as a
-- inner join T_BD_EXPENSE as b on b.FEXPID = a.F_DEV_FYXMBM
where a.FENTRYID in (
	select max(a2.FENTRYID)
	from DEV_t_StandardRate_E as a2
	inner join DEV_t_StandardRate as b2 on b2.FID = a2.FID
	where b2.F_DEV_USEORGID = @orgId and a2.F_DEV_PRICE > 0
	group by a2.F_DEV_FYXMBM
);

select a2.FENTRYID as '工单分录内码'
	, a2.F_QLTK_COSTCENTER as '成本中心内码'
	, a2.F_QLTK_EXPITEM as '费用项目内码'
	, a2.F_QLTK_ZYHD as '作业活动内码'
	, b.FBUSINESSTYPE as '成本中心属性'
	, c.成本项目内码
	, '单耗量' = a2.F_QLTK_QTY
	, d.单价
	, '单位成本' = a2.F_QLTK_QTY * isnull(d.单价, 1)
into #工单分录成本单耗量
from QLTK_t_Route as a2-- 生产订单工艺路线子单据体
inner join T_CB_COSTCENTER as b on b.FCOSTCENTERID = a2.F_QLTK_COSTCENTER
inner join #成本项目匹配方案2 as c on c.费用项目或作业活动内码 = iif(b.FBUSINESSTYPE = 5, a2.F_QLTK_ZYHD, a2.F_QLTK_EXPITEM)
left join #最新标准费率 as d on d.费用项目内码 = a2.F_QLTK_EXPITEM
inner join T_PRD_MOENTRY as e on e.FENTRYID = a2.FENTRYID
inner join T_PRD_MO as f on f.FID = e.FID
inner join #产品成本计算单 as g on g.工单编号 = f.FBILLNO and g.工单行号 = e.FSEQ
-- 	where a2.FEntryId in (
-- 		select b3.FENTRYID
-- 		from T_PRD_MO as a3
-- 		inner join T_PRD_MOENTRY as b3 on b3.FID = a3.FID
-- 		inner join #有效的工单分录 as c3 on c3.工单编号 = a3.FBILLNO and c3.工单行号 = b3.FSEQ
-- 	)

select a.工单分录内码, a.成本项目内码
	, '单位成本' = sum(单位成本)
into #工单分录成本单耗量2
from #工单分录成本单耗量 as a
group by a.工单分录内码, a.成本项目内码;

-- 取费用类成本项目的成本计算单数据
select a.*
into #费用类成本项目计算单
from #子项成本计算单 as a
-- 只取成本项目汇总行
where a.是否为材料类成本项目 = 0 and len(a.费用项目编码) !> 0;

select a.*, e.工单分录内码
	, e.成本项目内码
	, e.单位成本
into #费用类成本项目计算单2
from #费用类成本项目计算单 as a
inner join T_PRD_MO as b on b.FBILLNO = a.工单编号
inner join T_PRD_MOENTRY as c on c.FID = b.FID and c.FSEQ = a.工单行号
inner join T_HS_COSTITEM as d on d.FNUMBER = a.成本项目编码
left join #工单分录成本单耗量2 as e on e.工单分录内码 = c.FENTRYID and e.成本项目内码 = d.FCOSTITEMID;

select a.成本中心内码, a.产品编码, a.成本项目编码
	, '实际成本' = sum(本期完工_金额)
	, '目标单位成本' = avg(单位成本)
into #成本项目成本数据
from #费用类成本项目计算单2 as a
group by a.成本中心内码, a.产品编码, a.成本项目编码;

select a.*
	, b.实际产量
	, '实际单位成本' = iif(b.实际产量 != 0, a.实际成本 / b.实际产量, 0)
	, '目标成本' = a.目标单位成本 * b.实际产量
into #各产品的各费用总成本
from #成本项目成本数据 as a
inner join #各产品总产量 as b on b.成本中心内码 = a.成本中心内码 and b.产品编码 = a.产品编码;

select *
	, '超降率' = iif(目标成本 != 0, 超降额 / 目标成本, 0)
into #各产品的各费用总成本2
from (
	select *
		, '超降额' = 实际成本 - 目标成本
	from #各产品的各费用总成本
) t;

/************************************************************** 汇总 */
select a.FID, a.FNUMBER, b.FNAME, a.FPARENTID, a.FFULLPARENTID
into #物料分组
from T_BD_MATERIALGROUP as a
inner join T_BD_MATERIALGROUP_L as b on b.FLOCALEID = 2052 and b.FID = a.FID;

select b.FID as '分组内码1', b.FNUMBER as '分组编码1', b.FNAME as '分组名称1'
	, b.FPARENTID as '分组内码2', c.FNUMBER as '分组编码2', c.FNAME as '分组名称2'
	, c.FPARENTID as '分组内码3', d.FNUMBER as '分组编码3', d.FNAME as '分组名称3'
into #物料三级分组
from #物料分组 as b
---- 上面还有两级才算
--and isnull(len(b.FFULLPARENTID), 0) - isnull(len(replace(b.FFULLPARENTID, '.', '')), 0) = 2
left join #物料分组 as c on c.FID = b.FPARENTID
left join #物料分组 as d on d.FID = c.FPARENTID;

select a2.FNUMBER as '编码', b2.FNAME as '名称', b2.FSPECIFICATION as '规格型号'
	, a2.FMATERIALGROUP as '分组内码', c.FNAME as '基本单位名称'
into #物料
from T_BD_MATERIAL as a2
inner join T_BD_MATERIAL_L as b2 on b2.FLOCALEID = 2052 and b2.FMATERIALID = a2.FMATERIALID
inner join T_BD_MATERIALBASE as d on d.FMATERIALID = a2.FMATERIALID
inner join T_BD_UNIT_L as c on c.FLOCALEID = 2052 and c.FUNITID = d.FBASEUNITID
where a2.FUSEORGID = @orgId;

select b.FNUMBER as '成本中心编码', c.FNAME as '成本中心名称'
	, a.产品编码, d.名称 as '产品名称', d.规格型号
	, d.基本单位名称, d.分组名称1, d.分组名称2, d.分组名称3
	, a.成本项目编码, g.FNAME as '成本项目名称'
	, a.原料编码, e.名称 as '原料名称'
	, a.实际产量, a.实际总成本, a.实际单价, a.实际总消耗, a.实际单位成本, a.实际单耗
	, a.目标总成本, a.目标单位成本, a.目标总消耗量, a.目标单耗
	, a.超降额, '超降率' = round(a.超降率, 2) * 100
	, {seqField}
into {tableName}
from (
	select 成本中心内码, 产品编码, 成本项目编码
		, 原料编码
		, 实际产量, 实际总成本, 实际单价, 实际总消耗, 实际单位成本, 实际单耗
		, 目标总成本, 目标单位成本, 目标总消耗量, 目标单耗, 超降额, 超降率
	from #各产品的各原料总投入3

	union all

	select 成本中心内码, 产品编码, 成本项目编码
		, '原料编码' = ''
		, 实际产量, 实际成本, '实际单价' = 0, '实际总消耗' = 0, 实际单位成本, '实际单耗' = 0
		, 目标成本, 目标单位成本, '目标总消耗' = 0, '目标单耗' = 0, 超降额, 超降率
	from #各产品的各费用总成本2
) as a
inner join T_CB_COSTCENTER as b on b.FCOSTCENTERID = a.成本中心内码
inner join T_CB_COSTCENTER_L as c on c.FLOCALEID = 2052 and c.FCOSTCENTERID = a.成本中心内码
inner join (
	select a2.编码, a2.名称, a2.规格型号, a2.基本单位名称, b2.分组名称1, b2.分组名称2, b2.分组名称3
	from #物料 as a2
	left join #物料三级分组 as b2 on b2.分组内码1 = a2.分组内码
) as d on d.编码 = a.产品编码
left join #物料 as e on e.编码 = a.原料编码
inner join T_HS_COSTITEM as f on f.FNUMBER = a.成本项目编码
inner join T_HS_COSTITEM_L as g on g.FLOCALEID = 2052 and g.FCOSTITEMID = f.FCOSTITEMID
where 1 = 1;";
            // 执行SQL
            DBUtils.Execute(Context, sql);
        }

        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            // 将自定义过滤框内的过滤条件赋值到账表单据头的过滤条件中，以便将具体的过滤条件展示给用户
            var reportTitles = new ReportTitles();
            var customerFilter = filter.FilterParameter.CustomFilter;
            if (customerFilter == null)
            {
                return reportTitles;
            }

            var objOrg = customerFilter[F_QLTK_org.OrmKey] as DynamicObject;
            var orgName = PluginUtils.GetLocaleValue(objOrg);
            reportTitles.AddTitle(F_QLTK_org.FormKey, orgName);

            var year = customerFilter[F_QLTK_year.OrmKey]?.ToString();
            reportTitles.AddTitle(F_QLTK_year.FormKey, year);
            var month = customerFilter[F_QLTK_month.OrmKey]?.ToString();
            reportTitles.AddTitle(F_QLTK_month.FormKey, month);

            var endYear = customerFilter[F_QLTK_year_qtr.OrmKey]?.ToString();
            reportTitles.AddTitle(F_QLTK_year_re5.FormKey, endYear);
            var endMonth = customerFilter[F_QLTK_month_83g.OrmKey]?.ToString();
            reportTitles.AddTitle(F_QLTK_month_apv.FormKey, endMonth);

            var product = customerFilter[F_QLTK_Base_qtr.OrmKey] as DynamicObject;
            if (product != null)
            {
                var productName = PluginUtils.GetLocaleValue(product);
                reportTitles.AddTitle(F_QLTK_Text_re5.FormKey, productName);
            }

            return reportTitles;
        }

        private void FillCostData(anva11 filterParams, string tempTableName)
        {
            // 获取成本计算单数据
            var schemeId = KDConfigurations.目标成本报表获取成本计算单数据过滤方案(sql => DBUtils.ExecuteScalar<string>(Context, sql, default));
            //var schemeId = DBUtils.ExecuteScalar<string>(Context, $@"select top 1 FSCHEMEID
            //    from T_BAS_FILTERSCHEME
            //    where FFORMID = 'CB_CostCalBillHorizonRpt' 
            //    --and FUSERID = -1 and FSCHEMENAME = 'Default Scheme'
            //    and FISDEFAULT = 1;", default);
            var cbjsdReportData = ReportPluginHelper.GetReportData(Context, "CB_CostCalBill", "CB_CostCalFilter", schemeId, (reportCustomFilter, reportFilter) =>
            {
                // 设置参数
                var objOrg = PluginUtils.GenerateDynamicObjectById(Context, "ORG_Organizations", filterParams.OrgId.ToString());
                reportCustomFilter["ACCTGORGID"] = objOrg;
                reportCustomFilter["ACCTGORGID_ID"] = filterParams.OrgId;

                reportCustomFilter["Year"] = filterParams.StartYear;
                reportCustomFilter["Period"] = filterParams.StartMonth;
                reportCustomFilter["FEndYear"] = filterParams.EndYear;
                reportCustomFilter["FEndPeriod"] = filterParams.EndMonth;

                if (filterParams.ProductId > 0)
                {
                    var objProduct = PluginUtils.GenerateDynamicObjectById(Context, "BD_Material", filterParams.ProductId.ToString());
                    reportCustomFilter["STARTMATERIALID"] = objProduct;
                    reportCustomFilter["STARTMATERIALID_ID"] = filterParams.ProductId;
                    reportCustomFilter["ENDMATERIALID"] = objProduct;
                    reportCustomFilter["ENDMATERIALID_ID"] = filterParams.ProductId;
                }
            });

            //var t1 = cbjsdReportData.Select("FPRODUCTNO = '01.01.17.0001'");
            //var t3 = t1.Take(30);
            //var t4 = t3.CopyToDataTable();

            var tempTableFields = GetTempTableFields();
            PluginUtils.IntoNewTable(Context, tempTableName, tempTableFields, tempDataTable =>
            {
                foreach (DataRow row in cbjsdReportData.Rows)
                {
                    var newRow = tempDataTable.NewRow();
                    newRow["组织内码"] = row["facctgorgid"];
                    newRow["年期"] = row["FYearPeriodSeq"];
                    newRow["成本中心内码"] = row["FCOSTCENTERID"];
                    newRow["成本中心编码"] = row["FCOSTCENTERNUMBER"];
                    newRow["工单编号"] = row["FPRODUCTNO"];
                    newRow["工单类型"] = row["FPROORDERTYPE"];
                    newRow["工单行号"] = row["FBILLSeq"];
                    newRow["产品内码"] = row["FPRODUCTID"];
                    newRow["产品编码"] = row["FPRODUCTID_FNUMBER"];
                    newRow["成本项目编码"] = row["fcostitemid_fnumber"];
                    newRow["原料编码"] = row["FMATERIALFIELD_FNUMBER"];
                    newRow["费用项目编码"] = row["FEXPENSEITEMFIELD_FNUMBER"];
                    newRow["本期完工_数量"] = row["FCompleteQty"];
                    newRow["本期完工_单耗"] = row["FProductUseQty"];
                    newRow["本期完工_单位成本"] = row["FProductUseCost"];
                    newRow["本期完工_金额"] = row["FCompleteAmount"];
                    newRow["单据类型"] = row["FBILLTYPENAME"];
                    newRow["累计投入_数量"] = row["FSumInputQty"];
                    newRow["累计投入_金额"] = row["FSumInputAmount"];

                    tempDataTable.Rows.Add(newRow);
                }
            });
        }

        public override void CloseReportInstance()
        {
            base.CloseReportInstance();

            if (_sourceTableName == null)
            {
                return;
            }

            DropDbTable(_sourceTableName);
        }

        private List<DBFieldSchema> GetTempTableFields()
        {
            var fields = new List<DBFieldSchema>
            {
                new DBFieldSchema
                {
                    DbType = "int",
                    FieldName = "组织内码",
                    Type = typeof(int)
                },
                new DBFieldSchema
                {
                    DbType = "char(6)",
                    FieldName = "年期",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "int",
                    FieldName = "成本中心内码",
                    Type = typeof(int)
                },
                new DBFieldSchema
                {
                    DbType = "varchar(100)",
                    FieldName = "成本中心编码",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "varchar(100)",
                    FieldName = "工单编号",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "varchar(100)",
                    FieldName = "工单类型",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "int",
                    FieldName = "工单行号",
                    Type = typeof(int)
                },
                new DBFieldSchema
                {
                    DbType = "int",
                    FieldName = "产品内码",
                    Type = typeof(int)
                },
                new DBFieldSchema
                {
                    DbType = "varchar(100)",
                    FieldName = "产品编码",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "varchar(100)",
                    FieldName = "成本项目编码",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "varchar(100)",
                    FieldName = "原料编码",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "varchar(100)",
                    FieldName = "费用项目编码",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "decimal(23, 4)",
                    FieldName = "本期完工_数量",
                    Type = typeof(decimal)
                },
                new DBFieldSchema
                {
                    DbType = "decimal(23, 4)",
                    FieldName = "本期完工_单耗",
                    Type = typeof(decimal)
                },
                new DBFieldSchema
                {
                    DbType = "decimal(23, 4)",
                    FieldName = "本期完工_单位成本",
                    Type = typeof(decimal)
                },
                new DBFieldSchema
                {
                    DbType = "decimal(23, 4)",
                    FieldName = "本期完工_金额",
                    Type = typeof(decimal)
                },
                new DBFieldSchema
                {
                    DbType = "varchar(50)",
                    FieldName = "单据类型",
                    Type = typeof(string)
                },
                new DBFieldSchema
                {
                    DbType = "decimal(23, 4)",
                    FieldName = "累计投入_数量",
                    Type = typeof(decimal)
                },
                new DBFieldSchema
                {
                    DbType = "decimal(23, 4)",
                    FieldName = "累计投入_金额",
                    Type = typeof(decimal)
                }
            };
            return fields;
        }
    }
}
